---
title: Load provider relationships (parents or children)
summary: Loads parent or child provider-location relationships and applies works-for filtering.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
operation_id: OP-05
trigger: clsProviderLocation.ChildRelationships / ParentRelationships; clsCaseCost.WorksFor; clsRequestManager attorney lookup
---

# Load provider relationships (parents or children)

Loads either the child or parent set of provider-location relationships for a location, materializing the destination `clsProviderLocation` for each relationship, and applies "works for" filtering by relationship code and active-date window.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-05 |
| **Trigger** | `clsProviderLocation.ChildRelationships` / `ParentRelationships`; `clsCaseCost.WorksFor`; `clsRequestManager` attorney lookup |
| **Inputs** | `IDataPortalInfo`, provider-location ID |
| **Outputs** | `clsProviderRelationshipCollection` |
| **Primary components** | `clsProviderRelationshipCollection.GetChildProviderRelationshipCollection`, `GetParentProviderRelationshipCollection` |
| **Entry point** | `SAIF.Business.View.Provider/clsProviderRelationshipCollection.vb:236, 244` |

## Steps

1. Criteria stores `SourceRelationshipType` (`Parent` by default).
2. `RetrieveSQL` selects `RetrieveChildRelationshipsSQL` ([SQL-PREL-01](../sql/SQL-PREL-01-child-relationships.md)) when the source is the parent, otherwise `RetrieveParentRelationshipsSQL` ([SQL-PREL-02](../sql/SQL-PREL-02-parent-relationships.md)). Both join [PROVIDER_LOCATION](../database-tables.md#provider_location) to [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship) and left-join back to `PROVIDER_LOCATION` for the other end, ordered by business name.
3. `BeforeRetrieve` binds `:ProviderLocationID`.
4. `AfterRetrieve` builds a `clsProviderLocationCollection` alongside the relationship collection; each row produces a `clsProviderRelationship` whose `Load()` also materialises the destination `clsProviderLocation`.
5. `WorksFor` filters `ProviderRelationshipCode = "WRK"` and (when `ActiveOnly`, the default) `IsActive` ([BR-PREL-001](../business-rules.md#br-prel-001-works-for-relationships-are-wrk-code), [BR-PREL-002](../business-rules.md#br-prel-002-works-for-defaults-to-active-relationships-only)).
   - If `activeDate` is supplied: `WorksFor(activeDate)` substitutes `Date.MaxValue` for a missing termination date ([BR-PREL-003](../business-rules.md#br-prel-003-relationship-active-date-determination)).
   - Destination navigation always resolves to the opposite end via `DestinationRelationshipType` ([BR-PREL-004](../business-rules.md#br-prel-004-destination-relationship-type-is-the-opposite-of-the-source)).
   - Note: `clsProviderRelationship.Criteria` throws on *all five* SQL properties — individual relationships are never retrieved directly.**

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
	Start([Caller request]) --> S1{"Child or parent<br>relationships?"}
	S1 -->|Child| S2[RetrieveChildRelationshipsSQL]
	S1 -->|Parent| S3[RetrieveParentRelationshipsSQL]
	S2 --> S4["BeforeRetrieve binds<br>ProviderLocationID"]
	S3 --> S4
	S4 --> S5["AfterRetrieve: build<br>relationship + location<br>collections"]
	S5 --> S6{WorksFor filter requested?}
	S6 -->|Yes| S7[Filter code = WRK<br>and ActiveOnly date test]
	S6 -->|No| EndOk(["clsProviderRelationshipCollection<br>returned"])
	S7 --> EndOk
```

## Failure and edge-case behavior

- No matching relationships: an empty `clsProviderRelationshipCollection` is returned.
- `clsProviderRelationship.Criteria` throws `InvalidSQLProperty` for all five SQL properties, so a single relationship can never be retrieved directly through the portal.**
- A missing termination date is treated as `Date.MaxValue`, keeping open-ended relationships active ([BR-PREL-003](../business-rules.md#br-prel-003-relationship-active-date-determination)).

## Related documentation
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Sources
- `SAIF.Business.View.Provider/clsProviderRelationship.vb`
- `SAIF.Business.View.Provider/clsProviderRelationshipCollection.vb`